﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Player.MyAttribute;
using Player.DataTable;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
namespace storeProcedure
{
    public class AuditStoreList
    {
        protected static string AllColumns
        {
            get
            {
                List<string> columns = new List<string>();
                foreach (FieldInfo fi in typeof(Output).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
                {
                    ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                    if (attr != null)
                    {
                        columns.Add(attr.Name);
                    }
                }

                return string.Join(", ", columns.ToArray());
            }
        }

        public enum Variables : int
        {
        }

        public struct Input
        {
        }

        public struct Output
        {
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.Id)]
            public int Id;
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.Guid)]
            public Guid Guid;
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.Name)]
            public string Name;
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.TrustedAgentEnabled)]
            public bool TrustedAgentEnabled;
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.TrustedCollectorEnabled)]
            public bool TrustedCollectorEnabled;
            [ColumnMapping(typeof(AuditStore), (int)AuditStore.ActiveDatabaseId)]
            public int ActiveDatabaseId;
        }

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static IEnumerable<Output> Execute(string connecion, Input input)
        {
            // no permission checking

            string sql = SqlHelper.Build("SELECT", AllColumns, "FROM", typeof(AuditStore));
            using (SqlCommand command = new SqlCommand(sql))
            {
                //yield new Output();
                return new List<Output>();
                //SqlContext.Pipe.ExecuteAndSend(command);
            }
        }
    }

    public class AuditStoreDatabaseList
    {
        protected static string AllColumns
        {
            get
            {
                List<string> columns = new List<string>();
                foreach (FieldInfo fi in typeof(Output).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
                {
                    ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                    if (attr != null)
                    {
                        columns.Add(attr.Name);
                    }
                }

                return string.Join(", ", columns.ToArray());
            }
        }

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            AuditStoreId
        }

        public struct Input
        {
            [InputMapping((int)Variables.AuditStoreId)]
            public int AuditStoreId;

            public Input(int auditStoreId)
            {
                AuditStoreId = auditStoreId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Id)]
            public int Id;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.AuditStoreId)]
            public int AuditStoreId;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Guid)]
            public Guid Guid;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Name)]
            public string Name;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Server)]
            public string Server;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Database)]
            public string Database;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Type)]
            public int Type;
        }

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static IEnumerable<Output> Execute(string connectstring, Input input)
        {
            // no permission checking

            string sql = SqlHelper.Build("SELECT", AllColumns,
                                "FROM", typeof(AuditStoreDatabase),
                                "WHERE", AuditStoreDatabase.AuditStoreId, "=", Variables.AuditStoreId);

            using (SqlCommand command = new SqlCommand(sql))
            {
                //command.Parameters.AddRange(Parameters(new Input(auditStoreId)));
                //SqlContext.Pipe.ExecuteAndSend(command);
                return new List<Output>();
            }
        }
    }

    public class AuditStoreDatabaseListAll
    {
        protected static string AllColumns
        {
            get
            {
                List<string> columns = new List<string>();
                foreach (FieldInfo fi in typeof(Output).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
                {
                    ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
                    if (attr != null)
                    {
                        columns.Add(attr.Name);
                    }
                }

                return string.Join(", ", columns.ToArray());
            }
        }
        public enum Variables : int
        {
        }

        public struct Input
        {
        }

        public struct Output
        {
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Id)]
            public int Id;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.AuditStoreId)]
            public int AuditStoreId;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Guid)]
            public Guid Guid;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Name)]
            public string Name;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Server)]
            public string Server;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Database)]
            public string Database;
            [ColumnMapping(typeof(AuditStoreDatabase), (int)AuditStoreDatabase.Type)]
            public int Type;
        }

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static IEnumerable<Output> Execute(string connectstring, Input input)
        {
            // no permission checking
            string sql = SqlHelper.Build("SELECT", AllColumns, "FROM", typeof(AuditStoreDatabase));
            using (SqlCommand command = new SqlCommand(sql))
            {
                //command.Parameters.AddRange(Parameters(new Input()));
                //SqlContext.Pipe.ExecuteAndSend(command);
                return new List<Output>();
            }
        }
    }

    public class WindowsSessionGetMetadata
    {

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,

            [Variable(Direction.Input)]
            AuditStoreDatabaseId,

            [Variable(Direction.Output)]
            Exists,

            [Variable(Direction.Output)]
            DatabaseGuid,

            [Variable(Direction.Output)]
            IsHeadRecord,

            [Variable(Direction.Output)]
            EndTime,

            [Variable(Direction.Output)]
            MinSequence,

            [Variable(Direction.Output)]
            MaxSequence,

            [Variable(Direction.Output)]
            MinTimeStamp,

            [Variable(Direction.Output)]
            MaxTimeStamp,

            [Variable(Direction.Output)]
            AgentConnected
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditStoreDatabaseId)]
            public int AuditStoreDatabaseId;

            public Input(Guid sessionId, int auditStoreDatabaseId)
            {
                AuditStoreDatabaseId = auditStoreDatabaseId;
                SessionId = sessionId;
            }
        }

        public struct Output
        {
            [OutputMapping((int)Variables.Exists, SqlDbType.Bit)]
            public bool Exists;

            [OutputMapping((int)Variables.DatabaseGuid, SqlDbType.UniqueIdentifier)]
            public Guid DatabaseGuid;

            [OutputMapping((int)Variables.IsHeadRecord, SqlDbType.Bit)]
            public bool IsHeadRecord;

            [OutputMapping((int)Variables.EndTime, SqlDbType.DateTime)]
            public DateTime? EndTime;

            [OutputMapping((int)Variables.MinSequence, SqlDbType.Int)]
            public int? MinSequence;

            [OutputMapping((int)Variables.MaxSequence, SqlDbType.Int)]
            public int? MaxSequence;

            [OutputMapping((int)Variables.MinTimeStamp, SqlDbType.BigInt)]
            public long? MinTimeStamp;

            [OutputMapping((int)Variables.MaxTimeStamp, SqlDbType.BigInt)]
            public long? MaxTimeStamp;

            [OutputMapping((int)Variables.AgentConnected, SqlDbType.Bit)]
            public bool AgentConnected;
        }

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static WindowsSessionGetMetadata.Output ExecuteNonQuery(string connectionstring, Input input)
        {
            bool exists = false;
            Guid databaseGuid = Guid.Empty;
            //isHeadRecord = false;
            //endTime = SqlDateTime.Null;
            //minSequence = SqlInt32.Null;
            //maxSequence = SqlInt32.Null;
            //minTimeStamp = SqlInt64.Null;
            //maxTimeStamp = SqlInt64.Null;
            //agentConnected = false;

            // Check whether specified Session record exists in the database first
            string connectionString = null;
            string sql = null;

            try
            {
                //connectionString = GetAuditStoreDatabaseConnectionString(auditStoreDatabaseId);
                connectionString = Player.App.ConnectionString;
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    sql = SqlHelper.Build(
                        "IF EXISTS(SELECT", Session.SessionId, "FROM", typeof(Session), "WITH (NOLOCK)",
                        "WHERE", Session.SessionId, "=", Variables.SessionId, ")",
                        "SET", Variables.Exists, "= 1",
                        "ELSE SET", Variables.Exists, "= 0");
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        //command.Parameters.AddRange(Parameters(new Input(sessionId, auditStoreDatabaseId)));
                        command.ExecuteNonQuery();
                        return new Output();
                        //command.GetValue(Variables.Exists, ref exists);
                    }


                    if (exists)
                    {
                        //if (!CanReplayAuditTrail(sessionId, auditStoreDatabaseId))
                        //{
                        //    RaiseUnauthorizedException("You have no permission to replay this Windows session");
                        //}
                        // Get database GUID
                        using (SqlCommand command = new SqlCommand("DatabaseGuidGet", connection))
                        {
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add(new SqlParameter()
                            {
                                ParameterName = "@Guid",
                                SqlDbType = SqlDbType.UniqueIdentifier,
                                Direction = ParameterDirection.Output
                            });
                            command.ExecuteNonQuery();
                            databaseGuid = (Guid)command.Parameters["@Guid"].Value;
                        }

                        // Get session details
                        sql = SqlHelper.Build(
                            "SET", Variables.IsHeadRecord, "= 0",
                            "SET", Variables.AgentConnected, "= 0",
                            "SELECT",
                            SqlHelper.List(
                                SqlHelper.ToSql(Variables.IsHeadRecord, "=", Session.IsHeadRecord),
                                 SqlHelper.ToSql(Variables.EndTime, "=", Session.EndTime),
                                SqlHelper.ToSql(Variables.AgentConnected, "=",
                                      "CAST((CASE WHEN DATEDIFF(SECOND,", Agent.LastUpdateTime, ", GETUTCDATE()) <=",
                                      Agent.UpdateInterval, "THEN 1 ELSE 0 END) AS BIT)")
                                ),
                            "FROM", typeof(Session), "WITH (NOLOCK)",
                            "LEFT JOIN", typeof(Agent), "WITH (NOLOCK)", "ON", Session.AgentSid, "=", Agent.AgentSid,
                            "WHERE", Session.SessionId, "=", Variables.SessionId,

                            "SELECT",
                            Variables.MinSequence, "= MIN(", WashData.SequenceNumber, "),",
                            Variables.MaxSequence, "= MAX(", WashData.SequenceNumber, "),",
                            Variables.MinTimeStamp, "= MIN(", WashData.TimeStamp, "),",
                            Variables.MaxTimeStamp, "= MAX(", WashData.TimeStamp, ")",
                            "FROM", typeof(WashData), "WITH (NOLOCK)", "WHERE", WashData.SessionId, "=", Variables.SessionId);
                        using (SqlCommand command = new SqlCommand(sql, connection))
                        {
                            //command.Parameters.AddRange(Parameters(new Input(sessionId, auditStoreDatabaseId)));
                            command.ExecuteNonQuery();
                            return new Output();
                            //command.GetValue(Variables.IsHeadRecord, ref isHeadRecord);
                            //command.GetValue(Variables.EndTime, ref endTime);
                            //command.GetValue(Variables.MinSequence, ref minSequence);
                            //command.GetValue(Variables.MaxSequence, ref maxSequence);
                            //command.GetValue(Variables.MinTimeStamp, ref minTimeStamp);
                            //command.GetValue(Variables.MaxTimeStamp, ref maxTimeStamp);
                            //command.GetValue(Variables.AgentConnected, ref agentConnected);
                        }
                    }

                }
            }
            catch (SqlException e)
            {
                //RaiseError(e);
                throw e;
            }
        }
    }

    public class UnixSessionGetMetadata
    {
        #region Mapping definition

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,

            [Variable(Direction.Input)]
            AuditStoreDatabaseId,

            [Variable(Direction.Output)]
            Exists,

            [Variable(Direction.Output)]
            DatabaseGuid,

            [Variable(Direction.Output)]
            IsHeadRecord,

            [Variable(Direction.Output)]
            EndTime,

            [Variable(Direction.Output)]
            MinTimeStamp,

            [Variable(Direction.Output)]
            MaxTimeStamp,

            [Variable(Direction.Output)]
            MinSequence,

            [Variable(Direction.Output)]
            MaxSequence,

            [Variable(Direction.Output)]
            AgentConnected
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditStoreDatabaseId)]
            public int AuditStoreDatabaseId;

            public Input(Guid sessionId, int auditStoreDatabaseId)
            {
                AuditStoreDatabaseId = auditStoreDatabaseId;
                SessionId = sessionId;
            }
        }

        public struct Output
        {
            [OutputMapping((int)Variables.Exists, SqlDbType.Bit)]
            public bool Exists;

            [OutputMapping((int)Variables.DatabaseGuid, SqlDbType.UniqueIdentifier)]
            public Guid DatabaseGuid;

            [OutputMapping((int)Variables.IsHeadRecord, SqlDbType.Bit)]
            public bool IsHeadRecord;

            [OutputMapping((int)Variables.EndTime, SqlDbType.DateTime)]
            public DateTime? EndTime;

            [OutputMapping((int)Variables.MinTimeStamp, SqlDbType.BigInt)]
            public long? MinTimeStamp;

            [OutputMapping((int)Variables.MaxTimeStamp, SqlDbType.BigInt)]
            public long? MaxTimeStamp;

            [OutputMapping((int)Variables.MinSequence, SqlDbType.Int)]
            public int? MinSequence;

            [OutputMapping((int)Variables.MaxSequence, SqlDbType.Int)]
            public int? MaxSequence;

            [OutputMapping((int)Variables.AgentConnected, SqlDbType.Bit)]
            public bool AgentConnected;
        }
        #endregion

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static UnixSessionGetMetadata.Output ExecuteNonQuery(
           string connectionstring, Input input)
        {
            bool exists = false;
            Guid databaseGuid = Guid.Empty;
            //isHeadRecord = false;
            //endTime = SqlDateTime.Null;
            //minTimeStamp = SqlInt64.Null;
            //maxTimeStamp = SqlInt64.Null;
            //minSequence = SqlInt32.Null;
            //maxSequence = SqlInt32.Null;
            //agentConnected = false;

            string connectionString = null;
            string sql = null;

            try
            {
                connectionString = Player.App.ConnectionString;
                //AuditStoreDatabaseType auditDbType = GetAuditStoreDatabaseTypeById(auditStoreDatabaseId);
                AuditStoreDatabaseType auditDbType = AuditStoreDatabaseType.WindowsAndUnix;
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    // Check whether specified Session record exists in the database first
                    switch (auditDbType)
                    {
                        case AuditStoreDatabaseType.Unix:
                            // For V1 DirectAudit...     
                            sql = SqlHelper.Build(
                                "IF EXISTS(SELECT", USession.Id, "FROM", typeof(USession), "WITH (NOLOCK)",
                                "WHERE", USession.Id, "=", Variables.SessionId, ")",
                                "SET", Variables.Exists, "= 1",
                                "ELSE SET", Variables.Exists, "= 0");

                            break;
                        case AuditStoreDatabaseType.WindowsAndUnix:
                            sql = SqlHelper.Build(
                                "IF EXISTS(SELECT", Session.SessionId, "FROM", typeof(Session), "WITH (NOLOCK)",
                                "WHERE", Session.SessionId, "=", Variables.SessionId, ")",
                                "SET", Variables.Exists, "= 1",
                                "ELSE SET", Variables.Exists, "= 0");

                            break;
                    }

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        //command.Parameters.AddRange(Parameters(new Input(sessionId, auditStoreDatabaseId)));
                        command.ExecuteNonQuery();
                        return new Output();
                        //command.GetValue(Variables.Exists, ref exists);
                    }

                    if (exists)
                    {
                        // check permission
                        //if (!CanReplayAuditTrail(sessionId, auditStoreDatabaseId))
                        //{
                        //    RaiseUnauthorizedException("You have no permission to replay this Unix session");
                        //}

                        // Get session details
                        switch (auditDbType)
                        {
                            case AuditStoreDatabaseType.Unix:
                                // For V1 DirectAudit...     
                                sql = SqlHelper.Build(
                                    "DECLARE @MinTime DATETIME",
                                    "DECLARE @MaxTime DATETIME",
                                    "SET", Variables.IsHeadRecord, "= 1",
                                    "SET", Variables.AgentConnected, "= 1",

                                    "SELECT", Variables.EndTime, "=", USession.EndTime,
                                    "FROM", typeof(USession), "WITH (NOLOCK)",
                                    "WHERE", USession.Id, "=", Variables.SessionId,

                                    "SELECT",
                                    "@MinTime = MIN(", URawData.CapturedTime, "),",
                                    "@MaxTime = MAX(", URawData.CapturedTime, "),",
                                    Variables.MinSequence, "= MIN(", URawData.Sequence, "),",
                                    Variables.MaxSequence, "= MAX(", URawData.Sequence, ")",
                                    "FROM", typeof(URawData), "WITH (NOLOCK)",
                                    "WHERE", URawData.Session_Id, "=", Variables.SessionId,
                                    "AND", URawData.Sequence, "> 0",

                                    "SET", Variables.MinTimeStamp, "=", SqlHelper.DateTimeToBigInt("@MinTime"),
                                    "SET", Variables.MaxTimeStamp, "=", SqlHelper.DateTimeToBigInt("@MaxTime")
                                    );

                                break;
                            case AuditStoreDatabaseType.WindowsAndUnix:
                                // Otherwise...
                                // Get database GUID                            
                                using (SqlCommand command = new SqlCommand("DatabaseGuidGet", connection))
                                {
                                    command.CommandType = CommandType.StoredProcedure;
                                    command.Parameters.Add(new SqlParameter()
                                    {
                                        ParameterName = "@Guid",
                                        SqlDbType = SqlDbType.UniqueIdentifier,
                                        Direction = ParameterDirection.Output
                                    });
                                    command.ExecuteNonQuery();
                                    databaseGuid = (Guid)command.Parameters["@Guid"].Value;
                                }

                                sql = SqlHelper.Build(
                                    "SET", Variables.IsHeadRecord, "= 0",
                                    "SET", Variables.AgentConnected, "= 0",

                                    "SELECT",
                                   SqlHelper.List(
                                       SqlHelper.ToSql(Variables.IsHeadRecord, "=", Session.IsHeadRecord),
                                       SqlHelper.ToSql(Variables.EndTime, "=", Session.EndTime),
                                      SqlHelper.ToSql(Variables.AgentConnected, "=",
                                              "CAST((CASE WHEN DATEDIFF(SECOND,", Agent.LastUpdateTime,
                                              ", GETUTCDATE()) <=",
                                              Agent.UpdateInterval, "THEN 1 ELSE 0 END) AS BIT)")
                                        ),
                                    "FROM", typeof(Session), "WITH (NOLOCK)",
                                    "LEFT JOIN", typeof(Agent), "WITH (NOLOCK)", "ON", Session.AgentSid, "=", Agent.AgentSid,
                                    "WHERE", Session.SessionId, "=", Variables.SessionId,

                                    "SELECT",
                                    Variables.MinTimeStamp, "= MIN(", RawData.TimeStamp, "),",
                                    Variables.MaxTimeStamp, "= MAX(", RawData.TimeStamp, "),",
                                    Variables.MinSequence, "= MIN(", RawData.Sequence, "),",
                                    Variables.MaxSequence, "= MAX(", RawData.Sequence, ")",
                                    "FROM", typeof(RawData), "WITH (NOLOCK)",
                                    "WHERE", RawData.SessionId, "=", Variables.SessionId,
                                    "AND", RawData.Sequence, "> 0"
                                    );

                                break;
                        }

                        using (SqlCommand command = new SqlCommand(sql, connection))
                        {
                            //command.Parameters.AddRange(Parameters(new Input(sessionId, auditStoreDatabaseId)));
                            command.ExecuteNonQuery();
                            return new Output();

                            //command.GetValue(Variables.IsHeadRecord, ref isHeadRecord);
                            //command.GetValue(Variables.EndTime, ref endTime);
                            //command.GetValue(Variables.MinTimeStamp, ref minTimeStamp);
                            //command.GetValue(Variables.MaxTimeStamp, ref maxTimeStamp);
                            //command.GetValue(Variables.MinSequence, ref minSequence);
                            //command.GetValue(Variables.MaxSequence, ref maxSequence);
                            //command.GetValue(Variables.AgentConnected, ref agentConnected);
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                //RaiseError(e);
                throw e;
            }
        }
    }

    public class SessionGetSummary
    {
        #region Mapping definition

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Output)]
            Zone,
            [Variable(Direction.Output)]
            User,
            [Variable(Direction.Output)]
            Machine,
            [Variable(Direction.Output)]
            StartTime,
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                AuditDatabaseId = auditDatabaseId;
                SessionId = sessionId;
            }
        }

        public struct Output
        {
            [OutputMapping((int)Variables.Zone, SqlDbType.UniqueIdentifier)]
            public Guid Zone;
            [OutputMapping((int)Variables.User, SqlDbType.NVarChar, 511)]
            public string User;
            [OutputMapping((int)Variables.Machine, SqlDbType.NVarChar, 255)]
            public string Machine;
            [OutputMapping((int)Variables.StartTime, SqlDbType.DateTime)]
            public DateTime StartTime;
        }
        #endregion

        //[SqlProcedure(Name = "SessionGetSummary")]
        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static Output ExecuteNonQuery(string connectstring, Input input)
        {
            //if (!CanReadAuditTrail(sessionId, auditDatabaseId))
            //{
            //    RaiseUnauthorizedException("You have no permission to read this session");
            //}

            //zone = Guid.Empty;
            //user = string.Empty;
            //machine = string.Empty;
            //startTime = DateTime.MinValue; // DateTime.MinValue means <Unknown>

            string sql = string.Empty;
            AuditStoreDatabaseType auditStoreDatabaseType = AuditStoreDatabaseType.WindowsAndUnix;

            if (auditStoreDatabaseType == AuditStoreDatabaseType.WindowsAndUnix)
            {
                // this is V2 db
                sql = SqlHelper.Build("SELECT", Variables.Zone, "=", Session.Zone, ",",
                            Variables.User, "=", Session.UserName, ",",
                            Variables.Machine, "=", Session.MachineName, ",",
                            Variables.StartTime, "=", Session.StartTime,
                            "FROM", typeof(Session),
                            "WHERE", Session.SessionId, "=", Variables.SessionId
                    );
            }
            else if (auditStoreDatabaseType == AuditStoreDatabaseType.Unix)
            {
                // this is V1 db
                sql = SqlHelper.Build("SELECT", Variables.Zone, "=", USession.Zone, ",",
                            Variables.User, "=", USession.UserName, ",",
                            Variables.Machine, "=", USession.MachineName, ",",
                            Variables.StartTime, "=", USession.StartTime,
                            "FROM", typeof(USession),
                            "WHERE", USession.Id, "=", Variables.SessionId
                    );
            }
            else
            {
                throw new Exception("Unknown Audit Store type");
            }

            try
            {
                string connectionString = Player.App.ConnectionString;
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        //command.Parameters.AddRange(Parameters(new Input(sessionId, auditDatabaseId)));
                        command.ExecuteNonQuery();
                        return new Output();
                        //command.GetValue(Variables.Zone, ref zone);
                        //command.GetValue(Variables.User, ref user);
                        //command.GetValue(Variables.Machine, ref machine);
                        //command.GetValue(Variables.StartTime, ref startTime);
                    }
                }
            }
            catch (SqlException e)
            {
                //RaiseError(e);
                throw e;
            }
        }
    }

    public class AuditStoreActiveDatabaseHistoryList
    {
        #region Mapping definition

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            AuditStoreId
        }

        public struct Input
        {
            [InputMapping((int)Variables.AuditStoreId)]
            public int AuditStoreId;

            public Input(int auditStoreId)
            {
                AuditStoreId = auditStoreId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(AuditStoreActiveDatabaseHistory), (int)AuditStoreActiveDatabaseHistory.Guid)]
            public Guid Guid;
            [ColumnMapping(typeof(AuditStoreActiveDatabaseHistory), (int)AuditStoreActiveDatabaseHistory.ActiveTime)]
            public DateTime ActiveTime;
        }
        #endregion

        //[StoredProcedure(ExecuteAs = @"'NT Authority\System'", ExecuteBy = "user")]
        public static IEnumerable<Output> Execute(string connectstring, Input input)
        {
            string sql = SqlHelper.Build(
                "SELECT",
                SqlHelper.List(
                    AuditStoreActiveDatabaseHistory.Guid,
                    AuditStoreActiveDatabaseHistory.ActiveTime),
                "FROM", typeof(AuditStoreActiveDatabaseHistory),
                "WHERE", AuditStoreActiveDatabaseHistory.AuditStoreId, "=", Variables.AuditStoreId,
                "ORDER BY", AuditStoreActiveDatabaseHistory.ActiveTime);
            using (SqlCommand command = new SqlCommand(sql))
            {
                //command.Parameters.AddRange(Parameters(new Input(auditStoreId)));
                //SqlContext.Pipe.ExecuteAndSend(command);
                return new List<Output>();
            }
        }
    }
}